package com.ezops.poc.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Scanner;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.TransformerException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class EZOPSQueryGenerator {
	static String FIELD_INSERTION_QUERY = "INSERT INTO EZOPSDB.EZOPS_FIELDS(FIELD_NAME,TYPE,ASSET_ID) VALUES ('%s','NA',%d);";
	static String CLIENT_FIELD_INSERTION_QUERY = "INSERT INTO EZOPSDB.CLIENT_FIELDS(FIELD_NAME,CLIENT_ID,ASSET_ID) VALUES ('%s',1,%d);";
	static String MAPPING_INSERTON_QUERY = "INSERT INTO EZOPSDB.EZOPS_CLIENT_FIELD_MAPPING(EZOPS_FIELD_ID,CLIENT_FIELD_ID)  SELECT A.FIELD_ID, B.FIELD_ID FROM EZOPSDB.EZOPS_FIELDS A,  EZOPSDB.CLIENT_FIELDS B  WHERE lower(A.FIELD_NAME) LIKE '%s' AND A.ASSET_ID in ( %d,0) AND lower(B.FIELD_NAME) LIKE '%s' AND B.ASSET_ID in ( %d,0);";
	static String MAPPING_INSERTON_QUERY_TEMP = "INSERT INTO EZOPSDB.EZOPS_CLIENT_FIELD_MAPPING(EZOPS_FIELD_ID,CLIENT_FIELD_ID)  values (%d,%d);";

	// 82 - 145
	// 102 -165
	public static void main(String[] args) throws SAXException, IOException,
			ParserConfigurationException, TransformerException {
	//	generateTempQuery();
		String option = args[0];
		switch (option) {
		case "ezbond":
			generateInsertionQueryForFields("ezopsBondFields.xml", 1);
			break;
		case "ezequity":
			generateInsertionQueryForFields("ezopsEquityFields.xml", 2);
			break;
		case "ezbondmapping":
			generateInsertionForEquityMapping();
			break;
		case "eequitydmapping":
			generateInsertionForEquityMapping();
			break;
		case "otequitydmapping":
			generateInsertionForOtEquityMapping();
			break;
		case "otcliebtequitydmapping":
			generateInsertionForOtClientMapping();
			break;

		case "otmapping":
			generateInsertionForOTEquityMapping();
			break;
		}

	}

	private static void generateTempQuery() {
		int y = 81, x = 144;
		while (true) {
			System.out.println(String.format(MAPPING_INSERTON_QUERY_TEMP, ++x,
					++y));
			if(y==102) break;
		}
	}

	private static void generateInsertionForOTEquityMapping() {
		try {
			Scanner scannerclient = new Scanner(new File(
					"CITI_OT_EQUITY_Client_Mapping.csv"));
			Scanner scannerezops = new Scanner(new File(
					"CITI_OT_EQUITY_Client_Mapping.csv"));
			scannerclient.useDelimiter(",");
			scannerezops.useDelimiter(",");
			scannerezops.nextLine();
			String[] ezopsFields = scannerezops.nextLine().split(",");
			int i = -1;
			while (scannerclient.hasNext()) {
				System.out.println(String.format(MAPPING_INSERTON_QUERY,
						scannerclient.next(), 3, ezopsFields[++i], 3));

			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void generateInsertionForEquityMapping() {
		// Iterator<String> mappingItr =
		// ClientEzopsMapping.equityMapping.keySet()
		// .iterator();
		// while (mappingItr.hasNext()) {
		// String clientFiledName = mappingItr.next();
		// System.out.println(String.format(MAPPING_INSERTON_QUERY,
		// ClientEzopsMapping.equityMapping.get(clientFiledName), 2,
		// clientFiledName, 2));
		// }
	}

	private static void generateInsertionForOtEquityMapping() {
		try {
			Scanner scanner = new Scanner(new File("EzopsOtEquityFields.csv"));
			scanner.useDelimiter(",");
			while (scanner.hasNext()) {
				System.out.println(String.format(FIELD_INSERTION_QUERY,
						scanner.next(), 3));

			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void generateInsertionForOtClientMapping() {
		try {
			Scanner scanner = new Scanner(new File("CITI_OT_EQUITY_Test.csv"));
			scanner.useDelimiter(",");
			while (scanner.hasNext()) {
				System.out.println(String.format(CLIENT_FIELD_INSERTION_QUERY,
						scanner.next(), 3));

			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void generateInsertionQueryForFields(String fileName,
			int assetType) throws ParserConfigurationException, SAXException,
			IOException {
		DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory
				.newInstance();
		DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
		Document document = docBuilder.parse(new File(fileName));
		parse(document.getDocumentElement(), assetType);
	}

	public static void parse(Node node, int assetType) {
		// do something with the current node instead of System.out
		System.out.println(String.format(FIELD_INSERTION_QUERY,
				node.getNodeName(), assetType));

		NodeList nodeList = node.getChildNodes();
		for (int i = 0; i < nodeList.getLength(); i++) {
			Node currentNode = nodeList.item(i);
			if (currentNode.getNodeType() == Node.ELEMENT_NODE) {
				// calls this method for all the children which is Element
				parse(currentNode, assetType);
			}
		}
	}
}
